CREATE PROCEDURE [dbo].[asi_GenerateExpectedPaymentSet]
    (@newExpectedPaymentSetKey uniqueidentifier, -- used if no existing row
     @monthYear datetime, -- Month/Year to generate payments for
     @userKey uniqueidentifier, -- user to log as CreateBy UpdatedBy
     @batchSize integer, -- number of payments to set at a time
     @doNotLoop bit, -- if set to 1 will process the first batch and exit
     @clearExistingValues bit,  -- set to 1 to clear existing values
     @updateHeaderFlag bit -- set to 1 if the procedure should update the header
     )
AS
BEGIN
    DECLARE @expectedPaymentSetKey uniqueidentifier
    DECLARE @foundExistingExpectedPaymentSet bit
    DECLARE @monthDay integer
    DECLARE @paymentMonth integer
    DECLARE @paymentYear integer
    DECLARE @monthYearEnd datetime
    DECLARE @setStatusComplete smallint
    DECLARE @setStatusGenerating smallint
    DECLARE @setStatusCompleteWithError smallint
    DECLARE @paymentStatusPending smallint
    DECLARE @paymentStatusAwaiting smallint
    DECLARE @paymentStatusProcessed smallint
    DECLARE @errorMessage NVARCHAR(4000);
    DECLARE @errorSeverity INT;
    DECLARE @errorState INT;
    
    -- turn row(s) affected messages off
    SET NOCOUNT ON

    -- VALIDATE - MonthYear must be at the start of the month
    SET @monthDay = DATEPART(DAY,@monthYear)
    
    IF @monthDay<>1
    BEGIN
        RAISERROR ( N'MonthYear Date must have day set to 1',16,1);
        RETURN
    END

    -- Get the date part values    
    SET @paymentMonth = DATEPART(MONTH,@monthYear)
    SET @paymentYear = DATEPART(YEAR,@monthYear)
    
    SET @monthYearEnd = DATEADD(MONTH,1,@monthYear)
    SET @monthYearEnd = DATEADD(DAY,-1,@monthYearEnd)
    
    -- variables for the expected set status values
    SET @setStatusComplete = 1
    SET @setStatusGenerating = 2
    SET @setStatusCompleteWithError = 3

    -- set the payment status values
    SET @paymentStatusPending = 1
    SET @paymentStatusAwaiting = 2
    SET @paymentStatusProcessed = 3
    
    -- Check value of Batch Size
    IF @batchSize <=0
        SET @batchSize = 50

    -- do we already have a row for this month/year
    IF EXISTS (SELECT 1    FROM RecurringDonationExpectedPaymentSet WHERE ExpectedPaymentDate=@monthYear)
    BEGIN
        SET @foundExistingExpectedPaymentSet = 1
        SELECT @expectedPaymentSetKey=[RecurringDonationExpectedPaymentSetKey]
          FROM [RecurringDonationExpectedPaymentSet]
         WHERE [ExpectedPaymentDate]=@monthYear
            
        -- if this isn't the GUID passed in raise an error
        IF @newExpectedPaymentSetKey <> @expectedPaymentSetKey
        BEGIN
            RAISERROR ( N'The ExpectedPaymentSetKey value passed into the Procedure does not match the existing ExpectedPaymentSetKey',16,1);
            RETURN
        END
    END
    ELSE
    BEGIN
        -- doesn't already exist so use the new key passed in
        SET @foundExistingExpectedPaymentSet = 0
        IF @newExpectedPaymentSetKey IS NOT NULL
            SET @expectedPaymentSetKey = @newExpectedPaymentSetKey
        ELSE
            SET @expectedPaymentSetKey = NEWID()
    END
    
    -- DROP THE EXISTING VALUES    
    IF @clearExistingValues = 1 AND @foundExistingExpectedPaymentSet = 1
    BEGIN

        BEGIN TRANSACTION
        
        DELETE
          FROM [RecurringDonationExpectedPayment]
         WHERE [RecurringDonationExpectedPaymentSetKey] = @expectedPaymentSetKey
               AND [RecurringDonationExpectedPaymentStatusCode] <> @paymentStatusProcessed
        
        COMMIT TRANSACTION
    END

    -- Identify the donations for this month/year            
    DECLARE @recurringDonationTable TABLE (
        [StartDate] datetime NOT NULL,
        [EndDate] datetime NULL,
        [RecurringDonationFrequencyCode] integer NOT NULL,
        [RecurringDonationCommitmentKey] uniqueidentifier NOT NULL
    )
     
    ;WITH RecurringDonationTable AS (     
        SELECT StartDate, EndDate, RecurringDonationFrequencyCode, RecurringDonationCommitmentKey
          FROM RecurringDonationCommitment
         WHERE CommitmentStatusInd='A' AND
               StartDate<=@monthYearEnd AND
               (
                   EndDate IS NULL
               OR
                   EndDate>=@monthYear
               )
        UNION ALL      
        SELECT DATEADD(m, 12/RecurringDonationFrequencyCode, StartDate), EndDate, RecurringDonationFrequencyCode, RecurringDonationCommitmentKey
          FROM RecurringDonationTable a
         WHERE DATEADD(m, 12/RecurringDonationFrequencyCode, StartDate) <= ISNULL(EndDate,@monthYearEnd)
               AND DATEADD(m, 12/RecurringDonationFrequencyCode, StartDate)<=@monthYearEnd
    )
    INSERT INTO @recurringDonationTable (StartDate, EndDate, RecurringDonationFrequencyCode, RecurringDonationCommitmentKey)
    SELECT StartDate, EndDate, RecurringDonationFrequencyCode, RecurringDonationCommitmentKey
      FROM RecurringDonationTable
     WHERE StartDate<=@monthYearEnd AND StartDate>=@monthYear
    OPTION (maxrecursion 0);

    -- Insert Header - we do this in a separate transaction so if the detail fails we can
    -- update the header with a completedWithError status
    BEGIN TRANSACTION

    BEGIN TRY

        IF @foundExistingExpectedPaymentSet = 0
        BEGIN
            INSERT INTO [dbo].[RecurringDonationExpectedPaymentSet] ([RecurringDonationExpectedPaymentSetKey], [ExpectedPaymentDate],
                                                                     [RecurringDonationExpectedPaymentSetStatusCode], [CreatedByUserKey],
                                                                     [CreatedOn], [UpdatedByUserKey], [UpdatedOn])
             VALUES (@expectedPaymentSetKey, @monthYear, @setStatusGenerating, @userKey, GETDATE(), @userKey, GETDATE())
        END
        ELSE
        BEGIN
            UPDATE [dbo].[RecurringDonationExpectedPaymentSet]
               SET [UpdatedByUserKey] = @userKey, [UpdatedOn] = GETDATE(), [RecurringDonationExpectedPaymentSetStatusCode] = @setStatusGenerating
             WHERE [RecurringDonationExpectedPaymentSetKey] = @expectedPaymentSetKey
        END
        
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
    
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT
            @errorMessage = ERROR_MESSAGE(),
            @errorSeverity = ERROR_SEVERITY(),
            @errorState = ERROR_STATE();
        
        RAISERROR (@errorMessage, -- Message text.
               @errorSeverity, -- Severity.
               @errorState -- State.
               );
        
    END CATCH
    
    -- Now insert the expected payments rows
    BEGIN TRANSACTION
        
    BEGIN TRY
                
        DECLARE @isDone bit
        SET @isDone = 0
        WHILE (@isDone = 0)
        BEGIN

            INSERT INTO [dbo].[RecurringDonationExpectedPayment]([RecurringDonationExpectedPaymentKey], [RecurringDonationExpectedPaymentSetKey],
                                                                 [RecurringDonationCommitmentKey], [OpportunityKey], [DonationAmount], [PaymentMethod],
                                                                 [Distribution], [Appeal], [Campaign], [Fund], [Narrative], [MatchReference], [BranchSortCode],
                                                                 [BankAccountNumber], [BankAccountName], [ResultingTransNum], [ResultingTransLineNum],
                                                                 [RecurringDonationExpectedPaymentStatusCode], [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn])
             SELECT TOP (@batchSize) NEWID(), @expectedPaymentSetKey, c.RecurringDonationCommitmentKey, NULL, c.DonationAmount, c.PaymentMethod,
                  c.Distribution, c.Appeal, c.Campaign, c.Fund, N'', c.MatchReference, c.BranchSortCode, c.BankAccountNumber,
                                     c.BankAccountName, 0, 0, @paymentStatusAwaiting, @userKey, GETDATE(), @userKey, GETDATE()
               FROM RecurringDonationCommitment c
                    INNER JOIN @recurringDonationTable r ON c.RecurringDonationCommitmentKey = r.RecurringDonationCommitmentKey
                    LEFT OUTER JOIN RecurringDonationExpectedPayment e ON c.RecurringDonationCommitmentKey=e.RecurringDonationCommitmentKey
                         AND e.[RecurringDonationExpectedPaymentSetKey] = @expectedPaymentSetKey                   
              WHERE e.RecurringDonationCommitmentKey IS NULL -- filter out the existing expected payments
             
             IF @@ROWCOUNT = 0
                 SET @isDone = 1

             IF @@TRANCOUNT > 0
                 COMMIT TRANSACTION
            
             IF @doNotLoop = 1
                 BREAK
        END -- WHILE
        
        -- Update the header to say the processing is complete
        IF @isDone = 1 AND @updateHeaderFlag=1
        BEGIN
            BEGIN TRANSACTION
            
            -- Update the header row to say we have finished generating
            UPDATE [dbo].[RecurringDonationExpectedPaymentSet]
               SET [UpdatedByUserKey] = @userKey, [UpdatedOn] = GETDATE(),
                   [RecurringDonationExpectedPaymentSetStatusCode] = @setStatusComplete
             WHERE [RecurringDonationExpectedPaymentSetKey] = @expectedPaymentSetKey
             IF @@TRANCOUNT > 0
                 COMMIT TRANSACTION

        END
        
    END TRY
    BEGIN CATCH
    
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
   
        SELECT
            @errorMessage = ERROR_MESSAGE(),
            @errorSeverity = ERROR_SEVERITY(),
            @errorState = ERROR_STATE();

        IF @updateHeaderFlag = 1
        BEGIN
            BEGIN TRANSACTION
            
            -- Update the header row to say we have finished with errors
            UPDATE [dbo].[RecurringDonationExpectedPaymentSet]
               SET [UpdatedByUserKey] = @userKey, [UpdatedOn] = GETDATE(),
                   [RecurringDonationExpectedPaymentSetStatusCode] = @setStatusCompleteWithError
             WHERE [RecurringDonationExpectedPaymentSetKey] = @expectedPaymentSetKey
             IF @@TRANCOUNT > 0
                 COMMIT TRANSACTION
        END
                
        RAISERROR (@errorMessage, -- Message text.
               @errorSeverity, -- Severity.
               @errorState -- State.
               );
        
    END CATCH
    
END




GO
GRANT EXECUTE ON  [dbo].[asi_GenerateExpectedPaymentSet] TO [IMIS]
GO